package lectores;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class LectorCondicionesBCalidad {
    
    private File archivoExcel;
    private Workbook libroCondiciones;
    private String insertCamposQuery = "INSERT INTO condiciones (tipo, id_agente, id_promotor, plim_inf_autos1, plim_sup_autos1, pvalor_autos1, plim_inf_autos2, plim_sup_autos2, pvalor_autos2, plim_inf_autos3, "
                + "plim_sup_autos3, pvalor_autos3, plim_inf_autos4, plim_sup_autos4, pvalor_autos4, plim_inf_autos5, plim_sup_autos5, pvalor_autos5, plim_inf_autos6, plim_sup_autos6, pvalor_autos6,"
                + "plim_inf_danos1, plim_sup_danos1, pvalor_danos1, plim_inf_danos2, plim_sup_danos2, pvalor_danos2, plim_inf_danos3, plim_sup_danos3, pvalor_danos3, plim_inf_danos4, plim_sup_danos4, pvalor_danos4,plim_inf_danos5, plim_sup_danos5, pvalor_danos5,plim_inf_danos6, plim_sup_danos6, pvalor_danos6,"
                + "plim_inf_personas1, plim_sup_personas1, pvalor_personas1, plim_inf_personas2, plim_sup_personas2, pvalor_personas2, plim_inf_personas3, "
                + "plim_sup_personas3, pvalor_personas3, plim_inf_personas4, plim_sup_personas4, pvalor_personas4,plim_inf_personas5, plim_sup_personas5, pvalor_personas5,plim_inf_personas6, plim_sup_personas6, pvalor_personas6,"
                + "rechazos, enero, febrero, marzo, abril, mayo, "
                + "junio, julio, agosto, septiembre, octubre, noviembre, diciembre, meta_anual, adicional1, adicional2) VALUES ";
    private Statement st;
    
    public LectorCondicionesBCalidad(File archivoExcel) throws FileNotFoundException, IOException, SQLException, ExcelMalFormadoException {
        this.archivoExcel = archivoExcel;
        cargaArchivo();
        this.st.close();
    }
    
    private void cargaArchivo() throws FileNotFoundException, IOException, SQLException, ExcelMalFormadoException {
        //Se carga el libro Excel Workbook
        InputStream inp = new FileInputStream(this.archivoExcel);
        if(archivoExcel.getName().toLowerCase().endsWith(".xls")) {
            libroCondiciones = new HSSFWorkbook(new POIFSFileSystem(inp));
        } else {
            libroCondiciones = new XSSFWorkbook(inp);
        }
        
        //Se carga los datos a la base
        st = cargaBaseDeDatos();
        creaTablaCondiciones(st);
        llenaTablaCondiciones(st);
        libroCondiciones = null;
    }
    
    private Statement cargaBaseDeDatos() throws SQLException {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(LectorCondicionesBCalidad.class.getName()).log(Level.SEVERE, null, ex);
        }

        Connection conexion = DriverManager.getConnection("jdbc:hsqldb:bonos_calidad","sa","");
        return conexion.createStatement();
    }
    
    private void creaTablaCondiciones(Statement st) throws SQLException {
        st.executeUpdate("DROP TABLE IF EXISTS condiciones");
        String sql = "CREATE TABLE condiciones (tipo VARCHAR(10), id_agente INTEGER, id_promotor INTEGER, plim_inf_autos1 double, "
                + "plim_sup_autos1 double, pvalor_autos1 double, plim_inf_autos2 double, plim_sup_autos2 double, pvalor_autos2 double,"
                + "plim_inf_autos3 double, plim_sup_autos3 double, pvalor_autos3 double, plim_inf_autos4 double, plim_sup_autos4 double, pvalor_autos4 double,"
                + "plim_inf_autos5 double, plim_sup_autos5 double, pvalor_autos5 double,plim_inf_autos6 double, plim_sup_autos6 double, pvalor_autos6 double, "
                + "plim_inf_danos1 double, plim_sup_danos1 double, pvalor_danos1 double, plim_inf_danos2 double, "
                + "plim_sup_danos2 double, pvalor_danos2 double, plim_inf_danos3 double, plim_sup_danos3 double, pvalor_danos3 double,plim_inf_danos4 double, plim_sup_danos4 double, "
                + "pvalor_danos4 double, plim_inf_danos5 double, plim_sup_danos5 double, pvalor_danos5 double, plim_inf_danos6 double, plim_sup_danos6 double, pvalor_danos6 double,"
                + "plim_inf_personas1 double, plim_sup_personas1 double, pvalor_personas1 double, plim_inf_personas2 double, plim_sup_personas2 double, pvalor_personas2 double, plim_inf_personas3 double, "
                + "plim_sup_personas3 double, pvalor_personas3 double, plim_inf_personas4 double, plim_sup_personas4 double, pvalor_personas4 double,plim_inf_personas5 double, plim_sup_personas5 double, pvalor_personas5 double,"
                + "plim_inf_personas6 double, plim_sup_personas6 double, pvalor_personas6 double, "
                + "rechazos double, enero double, febrero double, marzo double, abril double, mayo double, "
                + "junio double, julio double, agosto double, septiembre double, octubre double, noviembre double, diciembre double, meta_anual double, adicional1 INTEGER, adicional2 INTEGER)";
        st.executeUpdate(sql);
    }
    
    public void llenaTablaCondiciones(Statement st) throws SQLException, ExcelMalFormadoException {
        Sheet hojaCondiciones = (Sheet) libroCondiciones.getSheetAt(0);
        
        if(hojaCondiciones != null) {
            int indiceEmpiezaRenglonDatos = 2;
            
            String tipo = "";
            int idAgente = 0;
            int idPromo = 0;
            double[] porc_lims_inf_autos = new double[6];
            double[] porc_lims_sup_autos = new double[6];
            double[] porc_bon_autos = new double[6];
            
            double[] porc_lims_inf_daños = new double[6];
            double[] porc_lims_sup_daños = new double[6];
            double[] porc_bon_daños = new double[6];
            
            double[] porc_lims_inf_personas = new double[6];
            double[] porc_lims_sup_personas = new double[6];
            double[] porc_bon_personas = new double[6];
            
            double rechazos = 0;
            double[] metas_meses = new double[12];
            double metaAnual = 0;
            int idAdicional1 = -1;
            int idAdicional2 = -1;            
            int k = 1;
            Row renglonActual;
            for(int i = indiceEmpiezaRenglonDatos; i <= hojaCondiciones.getLastRowNum(); i++) {
                renglonActual = hojaCondiciones.getRow(i);
                int celCount = -1;
                int celAct = -1;
                
                if(renglonActual == null) {
                    throw new ExcelMalFormadoException((i+1), 0, ExcelMalFormadoException.RENGLON_VACIO, archivoExcel);
                }
                
                try {
                    celCount = 0;
                    tipo = renglonActual.getCell(celCount).getStringCellValue();
                    
                    if(!tipo.equalsIgnoreCase("promotor") && !tipo.equalsIgnoreCase("agente")) {
                        throw new ExcelMalFormadoException(i, 0, ExcelMalFormadoException.DATO_INESPERADO, archivoExcel);
                    }
                    
                    celCount = 1;
                    idAgente = (int) renglonActual.getCell(celCount).getNumericCellValue();
                    celCount = 2;
                    idPromo = (int) renglonActual.getCell(celCount).getNumericCellValue();
                    
                    //Se leen los rangos de los porcentajes
                    //Para autos
                    celCount = 5;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_lims_inf_autos[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }                    
                    celCount = 6;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_lims_sup_autos[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }                    
                    celCount = 7;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_bon_autos[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }
                    
                    //Para daños
                    celCount = 23;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_lims_inf_daños[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }
                    celCount = 24;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_lims_sup_daños[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }
                    celCount = 25;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_bon_daños[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }
                    
                    //Para personas
                    celCount = 41;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_lims_inf_personas[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }
                    celCount = 42;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_lims_sup_personas[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }
                    celCount = 43;
                    for(int j = 0; j < 6; j++) {
                        celAct = celCount + (j * 3);
                        porc_bon_personas[j] = renglonActual.getCell(celAct).getNumericCellValue();
                    }
                    
                    celCount = 59;
                    rechazos = renglonActual.getCell(celCount).getNumericCellValue();
                    
                    celCount = 60;
                    for(int j = 0; j < metas_meses.length; j++) {
                        metas_meses[j] = renglonActual.getCell(celCount).getNumericCellValue();
                        celCount++;
                    }
                    
                    celCount = 72;
                    metaAnual = renglonActual.getCell(celCount).getNumericCellValue();
                    
                    celCount = 73;
                    idAdicional1 = renglonActual.getCell(celCount) != null ? (int)(renglonActual.getCell(celCount).getNumericCellValue()) : -1;
                    
                    celCount = 74;
                    idAdicional2 = renglonActual.getCell(celCount) != null ? (int)(renglonActual.getCell(celCount).getNumericCellValue()) : -1;
                    
                } catch(IllegalStateException ise) {
                    throw new ExcelMalFormadoException(i, celCount, ExcelMalFormadoException.TIPO_INESPERADO, archivoExcel);
                }
                
                String query = obtenInsertCondiciones(tipo, ""+idAgente, ""+idPromo, porc_lims_inf_autos[0], porc_lims_sup_autos[0], porc_bon_autos[0], porc_lims_inf_autos[1], porc_lims_sup_autos[1], porc_bon_autos[1],
                        porc_lims_inf_autos[2], porc_lims_sup_autos[2], porc_bon_autos[2],porc_lims_inf_autos[3], porc_lims_sup_autos[3], porc_bon_autos[3], porc_lims_inf_autos[4], porc_lims_sup_autos[4], porc_bon_autos[4], porc_lims_inf_autos[5], porc_lims_sup_autos[5], porc_bon_autos[5],
                        porc_lims_inf_daños[0], porc_lims_sup_daños[0], porc_bon_daños[0], porc_lims_inf_daños[1], porc_lims_sup_daños[1], porc_bon_daños[1],
                        porc_lims_inf_daños[2], porc_lims_sup_daños[2], porc_bon_daños[2], porc_lims_inf_daños[3], porc_lims_sup_daños[3], porc_bon_daños[3], porc_lims_inf_daños[4], porc_lims_sup_daños[4], porc_bon_daños[4], porc_lims_inf_daños[5], porc_lims_sup_daños[5], porc_bon_daños[5],
                        porc_lims_inf_personas[0], porc_lims_sup_personas[0], porc_bon_personas[0], porc_lims_inf_personas[1], porc_lims_sup_personas[1], porc_bon_personas[1],
                        porc_lims_inf_personas[2], porc_lims_sup_personas[2], porc_bon_personas[2], porc_lims_inf_personas[3], porc_lims_sup_personas[3], porc_bon_personas[3], porc_lims_inf_personas[4], porc_lims_sup_personas[4], porc_bon_personas[4], porc_lims_inf_personas[5], porc_lims_sup_personas[5], porc_bon_personas[5],
                        rechazos, metas_meses[0], metas_meses[1], metas_meses[2], metas_meses[3], metas_meses[4], metas_meses[5], metas_meses[6],
                        metas_meses[7], metas_meses[8], metas_meses[9], metas_meses[10], metas_meses[11], metaAnual, idAdicional1, idAdicional2);
                st.executeUpdate(query);
                System.out.print(k + ": ");
                System.out.println(query);
                k++;
            }
        }
    }
    
    public String obtenInsertCondiciones(String tipo, String id_agente, String id_promo, double inf_autos1, double sup_autos1, double valor_autos1, double inf_autos2, double sup_autos2, double valor_autos2,
            double inf_autos3, double sup_autos3, double valor_autos3, double inf_autos4, double sup_autos4, double valor_autos4, double inf_autos5, double sup_autos5, double valor_autos5, double inf_autos6, double sup_autos6, double valor_autos6,
            double inf_daños1, double sup_daños1, double valor_daños1, double inf_daños2, double sup_daños2, double valor_daños2, double inf_daños3,
            double sup_daños3, double valor_daños3, double inf_daños4, double sup_daños4, double valor_daños4, double inf_daños5, double sup_daños5, double valor_daños5, double inf_daños6, double sup_daños6, double valor_daños6,
            double inf_personas1, double sup_personas1, double valor_personas1, double inf_personas2, double sup_personas2, double valor_personas2, double inf_personas3,
            double sup_personas3, double valor_personas3, double inf_personas4, double sup_personas4, double valor_personas4, double inf_personas5, double sup_personas5, double valor_personas5, double inf_personas6, double sup_personas6, double valor_personas6,
            double rechazos, double enero, double febrero, double marzo, double abril, double mayo, double junio, double julio,
            double agosto, double septiembre, double octubre, double noviembre, double diciembre, double meta_anual, int idAdicional1, int idAdicional2){
        String query = insertCamposQuery + "'" + tipo + "'," + id_agente + "," + id_promo + "," + inf_autos1 + "," + sup_autos1 + "," + valor_autos1 + "," + inf_autos2 + "," + sup_autos2 + "," + valor_autos2 + ","
                      + inf_autos3 + "," + sup_autos3 + "," + valor_autos3 + "," + inf_autos4 + "," + sup_autos4 + "," + valor_autos4 + "," + inf_autos5 + "," + sup_autos5 + "," + valor_autos5 + "," + inf_autos6 + "," + sup_autos6 + "," + valor_autos6 + "," 
                      + inf_daños1 + "," + sup_daños1 + "," + valor_daños1 + "," + inf_daños2 + "," + sup_daños2 + "," + valor_daños2 + ","
                      + inf_daños3 + "," + sup_daños3 + "," + valor_daños3 + "," + inf_daños4 + "," + sup_daños4 + "," + valor_daños4 + "," + inf_daños5 + "," + sup_daños5 + "," + valor_daños5 + "," + inf_daños6 + "," + sup_daños6 + "," + valor_daños6 + "," 
                      + inf_personas1 + "," + sup_personas1 + "," + valor_personas1 + "," + inf_personas2 + "," + sup_personas2 + "," + valor_personas2 + ","
                      + inf_personas3 + "," + sup_personas3 + "," + valor_personas3 + "," + inf_personas4 + "," + sup_personas4 + "," + valor_personas4 + "," + inf_personas5 + "," + sup_personas5 + "," + valor_personas5 + "," + inf_personas6 + "," + sup_personas6 + "," + valor_personas6 + "," 
                      + rechazos + "," + enero + "," + febrero + "," + marzo + "," + abril + "," + mayo + "," + junio + "," + julio + ","
                      + agosto + "," + septiembre + "," + octubre + "," + noviembre + "," + diciembre + "," + meta_anual + "," + idAdicional1 + "," + idAdicional2;
        return query;
    }
    
}